package com.example.demo.repository;

import com.example.demo.entity.Kitting.KittingProduct;
import com.example.demo.entity.Kitting.KittingStorageArea;
import com.example.demo.entity.Kitting.KittingWA;
import com.example.demo.entity.Kitting.KittingWp;
import jakarta.transaction.Transactional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Repository
public interface KittingRepository extends JpaRepository<KittingStorageArea, Long> {

    @Query(value = "SELECT k.id, k.storage_area as storageArea, k.plan_code as planCode, " +
            "k.product_code as productCode, g.goods_name as goodsName, " +
            "g.design_number_ver_spec as designNumberVerSpec " +
            "FROM WA_kitting_storage_area k " +
            "LEFT JOIN V_goods g ON k.product_code = g.goods_code " +
            "WHERE k.product_code = :productCode;",
            nativeQuery = true)
    KittingProduct findKittingGoods(String productCode);

    @Query(value = "SELECT bill_no FROM WA_produce_bill_detail_wp WHERE id = :ID;", nativeQuery = true)
    String getBillNoByID(Long ID);

    @Query(value = "select bill_no from WA_produce_bill_master where bill_no = :billNo;", nativeQuery = true)
    String getBillNo(String billNo);

    @Query(value = "SELECT top 1 WA_produce_bill_master.plan_code " +
            "FROM WA_produce_bill_detail_wp A " +
            "INNER JOIN WA_produce_bill_master ON A.bill_no = WA_produce_bill_master.bill_no " +
            "WHERE WA_produce_bill_master.bill_type <> N'车间任务组合单' " +
            "AND A.bill_no = :code;", nativeQuery = true)
    String getPlanCodeByCode(String code);

    @Query(value = "SELECT b_user_working_area.working_area_code, b_working_area.working_area_name " +
            "FROM b_user_working_area " +
            "INNER JOIN b_working_area ON b_user_working_area.working_area_code = b_working_area.working_area_code " +
            "WHERE  b_user_working_area.user_code = :userID " +
            "AND b_working_area.property = :property;", nativeQuery = true)
    List<KittingWA> queryWaCode(String userID, String property);

    @Query(value = "select end_qty from WA_kitting_point_stock where product_bill_no = :productBillNo and kitting_point_code = :kittingPointCode;", nativeQuery = true)
    Integer queryEndQty(String productBillNo, String kittingPointCode);

    @Query(value = "SELECT IsNull( A.qty, 0) " +
            "FROM WA_produce_bill_master A WITH(NOLOCK) " +
            "WHERE A.bill_no = :productBillNo;",nativeQuery = true)
    int queryPlanQty(String productBillNo);

    @Query(value = "SELECT " +
            "    ISNULL(A.plan_code, '') AS plan_code, " +
            "    ISNULL(A.goods_code, '') AS goods_code, " +
            "    ISNULL(B.goods_name, '') AS goods_name,  " +
            "    ISNULL(B.design_number_ver_spec, '') AS design_number_ver_spec, " +
            "    ISNULL(A.qty, 0) AS plan_qty, " +
            "    ISNULL(A.product_code, '') AS product_code " +
            "FROM " +
            "    WA_produce_bill_master A WITH(NOLOCK) " +
            "    LEFT OUTER JOIN V_goods B WITH(NOLOCK) " +
            "        ON A.goods_code = B.goods_code " +
            "    LEFT OUTER JOIN plan_bill_detail C WITH(NOLOCK) " +
            "        ON A.plan_code = C.plan_code AND A.goods_code = C.goods_code " +
            "WHERE A.bill_no = :productBillNo;", nativeQuery = true)
    KittingProduct getProductByBillNo(String productBillNo);

    @Modifying
    @Transactional
    @Query(value = "insert into WA_kitting_storage_area(storage_area, plan_code, product_code) values(:storageArea, :planCode, :productCode);", nativeQuery = true)
    void addStorageArea(String storageArea, String planCode, String productCode);

    @Modifying
    @Transactional
    @Query(value = "INSERT INTO WA_kitting_point_in_list( " +
            "   product_bill_no, " +
            "   kitting_point_code, " +
            "   in_qty, " +
            "   over_in_qty, " +
            "   confirm_by, " +
            "   confirm_date " +
            ") " +
            "VALUES( " +
            "    :productBillNo, " +
            "    :kittingPointCode, " +
            "    :confirmQty, " +
            "    :overConfirmQty, " +
            "    :username, " +
            "    :confirmDate);", nativeQuery = true)
    int addPointList(String productBillNo, String kittingPointCode, int confirmQty, int overConfirmQty, String username, String confirmDate);

    @Query(value = "select product_code from WA_produce_bill_master with(NOLOCK) where bill_no = :productBillNo;", nativeQuery = true)
    String getProductCode(String productBillNo);

    @Modifying
    @Transactional
    @Query(value = "INSERT INTO WA_kitting_storage_area_list( " +
            "    storage_area, " +
            "    plan_code, " +
            "    product_bill_no, " +
            "    product_code, " +
            "    create_by, " +
            "    create_date " +
            ") " +
            "VALUES( " +
            "    :storageArea, " +
            "    :planCode, " +
            "    :productBillNo, " +
            "    :productCode, " +
            "    :username, " +
            "    :date " +
            ");", nativeQuery = true)
    int addStorageList(String storageArea, String planCode, String productBillNo, String productCode, String username, String date);

    @Query(value = "select kitting_qty from WA_produce_bill_kitting_qty where product_bill_no = :productBillNo and goods_code = :goodsCode;", nativeQuery = true)
    Integer getQty(String productBillNo, String goodsCode);

    @Query(value = "select top 1 plan_qty from WA_produce_bill_detail_wp where bill_no = :billNo", nativeQuery = true)
    BigDecimal getPlanQty(String billNo);

    @Query(value = "SELECT " +
            "WA_produce_bill_detail_wp.goods_code, " +
            "WA_produce_bill_detail_wp.wp_no, " +
            "WA_produce_bill_detail_wp.id, " +
            "WA_produce_bill_detail_wp.bill_no, " +
            "WA_produce_bill_detail_wp.wp_id, " +
            "WA_produce_bill_detail_wp.wp_code, " +
            "WA_produce_bill_detail_wp.plan_qty, " +
            "WA_produce_bill_detail_wp.complete_qty, " +
            "WA_produce_bill_detail_wp.good_qty, " +
            "WA_produce_bill_detail_wp.bad_qty, " +
            "WA_produce_bill_detail_wp.set_work_qty, " +
            "ISNULL(p_wp_batch_stock.in_process_qty, 0) as in_process_qty, " +
            "(CONVERT(varchar, WA_produce_bill_detail_wp.wp_no) + '、' + " +
            "(SELECT wp_name FROM b_working_procedure WHERE wp_code = WA_produce_bill_detail_wp.wp_code)) as wp_name, " +
            "b_goods.goods_name, " +
            "b_goods.design_number, " +
            "ISNULL((CASE WA_produce_bill_detail_wp.wp_no " +
            "    WHEN 1 THEN ( " +
            "        CASE " +
            "        WHEN WA_produce_bill_detail_wp.complete_qty - WA_produce_bill_detail_wp.set_work_qty > 0 THEN WA_produce_bill_detail_wp.plan_qty - WA_produce_bill_detail_wp.complete_qty " +
            "        WHEN WA_produce_bill_detail_wp.complete_qty - WA_produce_bill_detail_wp.set_work_qty <= 0 THEN WA_produce_bill_detail_wp.plan_qty - WA_produce_bill_detail_wp.set_work_qty " +
            "        END) " +
            "    ELSE ( " +
            "        CASE " +
            "        WHEN WA_produce_bill_detail_wp.complete_qty - WA_produce_bill_detail_wp.set_work_qty > 0 THEN p_wp_batch_stock.in_process_qty + WA_produce_bill_detail_wp.transfer_qty - WA_produce_bill_detail_wp.complete_qty " +
            "        WHEN WA_produce_bill_detail_wp.complete_qty - WA_produce_bill_detail_wp.set_work_qty <= 0 THEN p_wp_batch_stock.in_process_qty + WA_produce_bill_detail_wp.transfer_qty - WA_produce_bill_detail_wp.set_work_qty " +
            "        END) " +
            "END), 0) as not_set_work_qty " +
            "FROM WA_produce_bill_detail_wp " +
            "LEFT OUTER JOIN p_wp_batch_stock ON WA_produce_bill_detail_wp.bill_no = p_wp_batch_stock.product_bill_no AND WA_produce_bill_detail_wp.wp_id = p_wp_batch_stock.wp_id " +
            "LEFT OUTER JOIN b_goods ON WA_produce_bill_detail_wp.goods_code = b_goods.goods_code " +
            "WHERE WA_produce_bill_detail_wp.bill_no = :billNo", nativeQuery = true)
    List<KittingWp> getWpSchedule(String billNo);
}